<?php

/* @project : Template
 * @auther  : 青云
 * @date    : 2018/1/10
 * @desc    : 用户模型
 */

namespace app\common\model;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class Excel extends Base
{
    //PHPExcel实例化对象
    private static $PHPExcel = null;

    // 表头
    protected static $cols = [];

    // 数据
    protected static $data = [];

    // 偏移
    protected static $offset = ['top' => 1, 'left' => 0];

    // 表行名
    private static $cell_key = array(
        'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
        'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
        'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM',
        'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'
    );

    function __get($name)
    {
        if ($name == 'data') {
            return self::$data;
        }
    }

    protected static function init()
    {
        self::$PHPExcel = new Spreadsheet();
    }

    function load($file_name)
    {
        self::$PHPExcel = IOFactory::load($file_name);
    }

    function read($file_name)
    {
        self::$PHPExcel = IOFactory::load($file_name);
        self::$data     = self::$PHPExcel->getActiveSheet()->toArray('', true, true, true);

        $cols = self::$cols;
        foreach ($cols as $col) {
            if (isset($col['relation'])) {
                $relation        = $col['relation'];
                $relation_table  = $relation['table'];
                $relation_field  = $relation['field'];
                $$relation_table = model($relation_table)->column($relation_field);
            }
            $title[] = $col['title'];
        }

        $import_data[] = $title;

        $offset     = self::$offset;
        $sheet_data = self::$data;

        for ($i = $offset['top']; $i <= count($sheet_data); $i++) {

            $idx  = ord('A') + $offset['left'];
            $item = array();

            if (!empty($sheet_data[$i][chr($idx)])) {
                foreach ($cols as $key => $col) {
                    $location = chr($idx + $key);

                    if (isset($col['relation'])) {
                        $relation       = $col['relation'];
                        $relation_table = $relation['table'];
                        if (!empty($sheet_data[$i][$location])) {
                            $item[$col['field']] = array_search($sheet_data[$i][$location], $$relation_table);
                        }
                    } else {
                        $item[$col['field']] = $sheet_data[$i][$location];
                    }
                }
                $import_data[] = $item;
            }
        }
        self::$data = $import_data;
    }

    function cols($cols)
    {
        self::$cols = $cols;
    }

    function data($data, $value = null)
    {
        self::$data = $data;

        return $this;
    }

    function offset($offset)
    {
        self::$offset = $offset;

        return $this;
    }

    function save_as($file_name)
    {
        $cols = self::$cols;

        $offset = self::$offset;

        $top = $offset['top'];
        foreach ($cols as $key => $col) {
            $location = self::$cell_key[$key + $offset['left']] . $top;
            self::$PHPExcel->setActiveSheetIndex(0)->setCellValue($location, $col['title']);
        }

        $data = self::$data;

        foreach ($data as $val) {
            $top++;
            foreach ($cols as $key => $col) {
                $location = self::$cell_key[$key + $offset['left']] . $top;

                //判断二维数组
                $fields = explode('.', $col['field']);
                if (count($fields) == 2) $value = $val[$fields[0]][$fields[1]];
                else $value  = $val[$col['field']];

                self::$PHPExcel->setActiveSheetIndex(0)->setCellValue($location, $value);
            }
        }


        $writer = IOFactory::createWriter(self::$PHPExcel, 'Xlsx');

       // $writer->save('php://output');

        $writer->save($file_name);
    }

    function download($file_name = '导出.xlsx')
    {
        $cols = self::$cols;

        $offset = self::$offset;

        $top = $offset['top'];
        
        foreach ($cols as $key => $col) {
            $location = self::$cell_key[$key + $offset['left']] . $top;
            self::$PHPExcel->setActiveSheetIndex(0)->setCellValue($location, $col['title']);
        }

        $data = self::$data;
        foreach ($data as $val) {
            $top++;
            foreach ($cols as $key => $col) {
                $location = self::$cell_key[$key + $offset['left']] . $top;

                //判断二维数组
                $fields = explode('.', $col['field']);
                if (count($fields) == 2) $value = $val[$fields[0]][$fields[1]];
                else $value  = $val[$col['field']];

                self::$PHPExcel->setActiveSheetIndex(0)->setCellValue($location, $value."\t");
            }
        }

        ob_clean();
        header("Content-Type: application/force-download");
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header("Content-Disposition:attachment;filename =" . str_ireplace('+', '%20', URLEncode($file_name)));
        header('Cache-Control: max-age=0');

        $writer = IOFactory::createWriter(self::$PHPExcel, 'Xlsx');

        $writer->save('php://output');
    }

    function downloads($file_name = '导出.xlsx')
    {
        $cols = self::$cols;

        $offset = self::$offset;

        $top = $offset['top'];
        foreach ($cols as $key => $col) {
            $location = self::$cell_key[$key + $offset['left']] . $top;
            self::$PHPExcel->setActiveSheetIndex(0)->setCellValue($location, $col['title']);
        }

        $data = self::$data;
        halt($data);
        foreach ($data as $val) {
            $top++;
            foreach ($cols as $key => $col) {
                $location = self::$cell_key[$key + $offset['left']] . $top;

                //判断二维数组
                $fields = explode('.', $col['field']);
                if (count($fields) == 2) $value = $val[$fields[0]][$fields[1]];
                else $value  = $val[$col['field']];

                self::$PHPExcel->setActiveSheetIndex(0)->setCellValue($location, $value."\t");
            }
        }

        ob_clean();
        header("Content-Type: application/force-download");
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header("Content-Disposition:attachment;filename =" . str_ireplace('+', '%20', URLEncode($file_name)));
        header('Cache-Control: max-age=0');

        $writer = IOFactory::createWriter(self::$PHPExcel, 'Xlsx');

        $writer->save('php://output');
    }

}
